# -*- coding:utf-8 -*-
import os.path

import pandas as pd

from infrastructure.utils.dateUtil import generatePastDateString
from infrastructure.utils.dbUtil import SqliteUtil, SqliteConfig
from config.baseConfiguration import Sqlite3Config, ReportConfig
from infrastructure.utils.log import logger

config = SqliteConfig(dbPath=Sqlite3Config.dbPath)
filename = '板块分析.xlsx'
filename = os.path.join(ReportConfig.reportPath, filename)


def groupView():
    db = SqliteUtil.get_instance(dbConfig=config)
    sql = f"""
        select
            r.dti as '交易日期'
            ,i.name as 板块名称
            ,r.group_id as 板块id
            ,r.ulbc as 涨停数
            ,r.dlbc as 跌停数
            ,r.mculbc as 连扳高度
            ,r.logic as 逻辑
            ,r.sbs as 首板数
            ,r.lb2s as '2连扳数'
            ,r.lb3s as '3连扳数'
            ,r.lb4ps as '3+连数'
            ,r.ubtop as 'top'
            ,r.ub5d as '5top'
            ,r.ub10d as '10top'
            ,r.ub20d as '20top'
            ,r.ub30d as '30top'
            ,r.ub60d as '60top'
            ,r.ub120d as '120top'
            ,r.ub240d as '240top'
            ,r.ub480d as '480top'
        from group_rotation r left join group_info i on r.group_id=i.id
        where 1=1
        --     and i.name = ''
        --     and r.dti =
        order by group_id, r.dti desc
        """
    dtsql = "select dti from market_sentiment order by dti desc limit 3"
    dtr = db.get_all(dtsql)
    if dtr is not None and len(dtr) > 0:
        dts = [str(dt[0]) for dt in dtr]
        sql = sql.replace("1=1", f"r.dti in ({','.join(dts)})")

    groups = db.get_all(sql)
    columns = list(zip(*db.cur.description))[0]
    if len(groups) == 0:
        return
    else:
        groups = [list(g) for g in groups]

    df = pd.DataFrame(groups, columns=columns)
    if os.path.exists(filename):
        with pd.ExcelWriter(filename, mode='a', if_sheet_exists='replace') as writer:
            df.to_excel(writer, sheet_name="板块轮动", index=False)
    else:
        with pd.ExcelWriter(filename, mode='w') as writer:
            df.to_excel(writer, sheet_name="板块轮动", index=False)


def groupTopView():
    db = SqliteUtil.get_instance(dbConfig=config)
    # 构造涨停数前5的SQL查询语句
    ztTopsql = f"""
        select dti, group_concat(names, ', ') as 涨停数
        from (
            SELECT r.dti, r.ulbc || '.' || i.name || '[' || group_id || ']' as names, r.ulbc
            from group_rotation r
                         left join group_info i on r.group_id = i.id
            where r.ulbc > 4
            order by r.ulbc desc
            )
        group by dti order by dti desc
        """

    # 构造连扳数前5的SQL查询语句
    lbTopsql = f"""
        select dti, group_concat(names, ', ') 连扳高度
        from (
            SELECT r.dti, r.mculbc || '.' || i.name || '[' || group_id || ']' as names, r.mculbc
            from group_rotation r
                left join group_info i on r.group_id = i.id
            where r.mculbc > 1
            order by r.mculbc desc
            )
        group by dti order by dti desc
        """

    # 执行涨停数前5的SQL查询并获取结果
    rows = db.get_all(ztTopsql)
    # 获取结果列名
    columns = list(zip(*db.cur.description))[0]
    # 如果结果为空，则直接返回
    if len(rows) == 0:
        return
    else:
        # 将结果转换为列表形式
        rows = [list(row) for row in rows]

    # 执行连扳数前5的SQL查询并获取结果
    rows1 = db.get_all(lbTopsql)
    # 获取结果列名
    columns1 = list(zip(*db.cur.description))[0]
    # 如果结果为空，则直接返回
    if len(rows1) == 0:
        return
    else:
        # 将结果转换为字典形式，以dti为键，其他列为值
        rows1 = {row[0]: list(row) for row in rows1}
    # 将两个结果合并
    rows = [list(row) + rows1[row[0]][1:] for row in rows]
    # 合并列名
    columns = columns + columns1[1:]
    # 将结果转换为DataFrame
    df = pd.DataFrame(rows, columns=columns)
    # 设置Excel表格名称
    sheet_name = "板块top"
    # 如果文件已存在，则追加写入数据
    if os.path.exists(filename):
        with pd.ExcelWriter(filename, mode='a', if_sheet_exists='replace') as writer:
            df.to_excel(writer, sheet_name=sheet_name, index=False)
    # 如果文件不存在，则创建文件并写入数据
    else:
        with pd.ExcelWriter(filename, mode='w') as writer:
            df.to_excel(writer, sheet_name=sheet_name, index=False)


def groupScrollView():
    db = SqliteUtil.get_instance(dbConfig=config)
    sdti = generatePastDateString(180)
    columns = {'ulbc': '涨停', 'mculbc': '连扳'}
    for c, v in columns.items():
        sql = f"""
            select dti
            ,i.name
            ,i.id
            ,case when i.type==0 then '概' when i.type==1 then '行' else '区' end as 'type'
            ,r.{c}
            from group_rotation r
                left join group_info i on r.group_id=i.id
            where dti > {sdti}
            order by dti desc
        """
        rows = db.get_all(sql)
        df = pd.DataFrame(rows, columns=['交易日期', '板块名称', '板块编码', '板块类型', 'value'])
        gids = df['板块编码'].unique().tolist()
        dfR = pd.DataFrame(index=sorted(set(df['交易日期'].values), reverse=True))
        for id in gids:
            name = df[df['板块编码'] == id]['板块名称'].values[0]
            type = df[df['板块编码'] == id]['板块类型'].values[0]
            dfT = df.loc[df['板块编码'] == id].set_index('交易日期').drop(columns=['板块名称', '板块编码', '板块类型']).rename(
                columns={'value': f"[{type}]{name}[{id}]"})
            try:
                dfR = pd.concat([dfR, dfT], axis=1)
            except Exception as e:
                logger.error(e)
        dfR = dfR.T
        sheet_name = f"板块{v}"
        if os.path.exists(filename):
            with pd.ExcelWriter(filename, mode='a', if_sheet_exists='replace') as writer:
                dfR.to_excel(writer, sheet_name=sheet_name, index=True)
        # 如果文件不存在，则创建文件并写入数据
        else:
            with pd.ExcelWriter(filename, mode='w') as writer:
                dfR.to_excel(writer, sheet_name=sheet_name, index=True)


def boardsView():
    db = SqliteUtil.get_instance(dbConfig=config)
    sql = f"""
        select b.dti as '交易日期'
                ,i.name as 股票名称, i.code as 股票编码
                ,case when b.type = 1 then '涨停' else '跌停' end as 封板类型
                ,b.count as 连扳数
                ,b.jtjb as 几天几板
                ,b.rise as 涨幅
                ,b.price as 现价
                ,b.first_time as 首次封板时间
                ,b.last_time as 最终封板时间
                ,b.open_count as 开板次数
                ,round(b.board_turnover/100000000, 2) as '封单额(亿)'
                ,round(b.market_capitalization/100000000, 2) as '流通市值(亿)'
                ,round(b.cje/100000000, 2) as '成交额(亿)'
                ,b.fcb as 封成比
                ,b.turnoverr as 换手
                ,b.board_state as 封板状态
                ,round(m.zf_open, 2) as 次日开盘涨幅
                ,round(m.zf_high, 2) as 次日最高涨幅
                ,round(m.zf_low, 2) as 次日最低涨幅
                ,round(m.zf_close, 2) as 次日收盘涨幅
                ,(select group_concat(r, '+')
                    from (
                        select '['|| bl.group_id||']'||gi.name as r
                        from stock_board_logic bl left join group_info gi on bl.group_id=gi.id
                        where bl.dti=b.dti and bl.code=b.code and bl.type=0
                    )) as 所属板块
                ,(select group_concat(r, '\n')
                    from (
                        select bl.info as r
                        from stock_board_logic bl 
                        where bl.dti=b.dti and bl.code=b.code and bl.type=1
                    )) as 涨停逻辑
        from stock_board b 
        left join stock_move m on m.code=b.code and m.dti=b.tdti
        left join stock_info i on b.code = i.code
        where 1=1
        --     and b.dti in ()
        --     and b.code = ''
        order by b.dti desc, b.first_time asc
        """
    dtsql = "select dti from market_sentiment order by dti desc limit 7"
    dtr = db.get_all(dtsql)
    if dtr is not None and len(dtr) > 0:
        dts = [str(dt[0]) for dt in dtr]
        sql = sql.replace("1=1", f"b.dti in ({','.join(dts)})")

    boards = db.get_all(sql)
    columns = list(zip(*db.cur.description))[0]
    if len(boards) == 0:
        return
    else:
        boards = [list(g) for g in boards]

    df = pd.DataFrame(boards, columns=columns)
    if os.path.exists(filename):
        with pd.ExcelWriter(filename, mode='a', if_sheet_exists='replace') as writer:
            df.to_excel(writer, sheet_name="封板分析", index=False)
    else:
        with pd.ExcelWriter(filename, mode='w') as writer:
            df.to_excel(writer, sheet_name="封板分析", index=False)


def spaceBoardView():
    db = SqliteUtil.get_instance(dbConfig=config)
    sql = f"""
        select dti as 日期, group_concat(rc, ', ') as 代码, group_concat(r, ', ') as 名称
        from (
            select b.dti, b.count || '.' || i.code rc, b.count || '.' || i.name as r, b.count
            from stock_board b
                   left join stock_info i on b.code = i.code
            where b.type = 1 and b.count >= 3
            order by count desc
        ) group by dti
        order by dti desc
        """

    boards = db.get_all(sql)
    columns = list(zip(*db.cur.description))[0]
    if len(boards) == 0:
        return
    else:
        boards = [list(g) for g in boards]

    df = pd.DataFrame(boards, columns=columns)
    sheet_name = "空间分析"
    if os.path.exists(filename):
        with pd.ExcelWriter(filename, mode='a', if_sheet_exists='replace') as writer:
            df.to_excel(writer, sheet_name=sheet_name, index=False)
    else:
        with pd.ExcelWriter(filename, mode='w') as writer:
            df.to_excel(writer, sheet_name=sheet_name, index=False)


def groupTip():
    db = SqliteUtil.get_instance(dbConfig=config)
    # 构造SQL查询语句
    sql = """
        select
            t.dti '交易日期'
            ,i.id 板块id
            ,i.name 板块名称
            ,t.tip 提示
            ,t.desc 描述
        from stock_tips t
            left join group_info i on t.tid = i.id
        where t.dti in (select distinct dti from stock_tips order by dti desc limit 3) 
            and t.type == 2
        order by t.dti desc
        """

    # 执行SQL查询，获取数据
    datas = db.get_all(sql)

    # 如果数据不为空且长度大于0
    if datas is not None and len(datas) > 0:
        # 获取数据列的标题
        columns = list(zip(*db.cur.description))[0]

        # 将数据转换为DataFrame
        df = pd.DataFrame(datas, columns=columns)

        # 判断文件是否存在
        if os.path.exists(filename):
            # 如果文件存在，则以追加模式打开Excel文件，并替换同名sheet
            with pd.ExcelWriter(filename, mode='a', if_sheet_exists='replace') as writer:
                df.to_excel(writer, sheet_name="板块提示", index=False)
        else:
            # 如果文件不存在，则以写入模式打开Excel文件
            with pd.ExcelWriter(filename, mode='w') as writer:
                df.to_excel(writer, sheet_name="板块提示", index=False)


def hotGroup():
    db = SqliteUtil.get_instance(dbConfig=config)
    # 构造SQL查询语句
    sql = """
        select
            '[' ||case when i.type==0 then '概' when i.type==1 then '行' else '区' end || ']' || i.name  as '板块名称' 
            ,i.id ID
            ,r.sdti 开始日期
            ,gr.ulbc 涨停
            ,gr.sbs 首板
            ,gr.lb2s '2板'
            ,gr.lb3s '3板'
            ,gr.lb4ps '3+板'
            ,gr.mculbc '最高板'
        from group_rise r
            left join main.group_rotation gr on r.group_id = gr.group_id and r.sdti = gr.dti
            left join group_info i on r.group_id = i.id
        where r.edti is null
        order by r.sdti desc
        """

    # 执行SQL查询，获取数据
    datas = db.get_all(sql)

    # 如果数据不为空且长度大于0
    if datas is not None and len(datas) > 0:
        # 获取数据列的标题
        columns = list(zip(*db.cur.description))[0]

        # 将数据转换为DataFrame
        df = pd.DataFrame(datas, columns=columns)
        sheet_name = "热点板块"
        # 判断文件是否存在
        if os.path.exists(filename):
            # 如果文件存在，则以追加模式打开Excel文件，并替换同名sheet
            with pd.ExcelWriter(filename, mode='a', if_sheet_exists='replace') as writer:
                df.to_excel(writer, sheet_name=sheet_name, index=False)
        else:
            # 如果文件不存在，则以写入模式打开Excel文件
            with pd.ExcelWriter(filename, mode='w') as writer:
                df.to_excel(writer, sheet_name=sheet_name, index=False)


def groupViewMain():
    groupView()
    boardsView()
    groupTip()
    groupTopView()
    groupScrollView()
    hotGroup()


if __name__ == "__main__":
    groupViewMain()